package com.gzhryc.common.excel;

import com.gzhryc.common.ClassTools;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

public class ExcelTools {

    public final static String DATA = "data";
    public final static String STYLE = "style";

    public static Object getCellValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    cellValue = cell.getRichStringCellValue().getString().trim();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    DecimalFormat df = new DecimalFormat("#");
                    cellValue = df.format(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue();
                    } else {
                        cellValue = cell.getCellFormula();
                    }
                    break;
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }

        return cellValue;
    }

    public static boolean export(OutputStream out, List<Map<String, Object>> data) {
        if (data.size() > 0) {
            String[] rowName = data.get(0).keySet().toArray(new String[data.get(0).size()]);
            List<Object[]> dataList = new ArrayList<Object[]>();
            for (Map<String, Object> item : data) {
                Object[] objArray = new Object[rowName.length];
                for (int i = 0; i < rowName.length; i++) {
                    objArray[i] = item.get(rowName[i]);
                }
                dataList.add(objArray);
            }
            return export(out, "工作表1", null, rowName, dataList);
        }
        return false;
    }

    public static boolean export(OutputStream out, String[] rowName, List<Object[]> dataList) {
        return export(out, "工作表1", null, rowName, dataList);
    }

    public static boolean export(OutputStream out, String sheetName, String title, String[] rowName,
                                 List<Object[]> dataList) {
        try {
            Workbook workbook = new SXSSFWorkbook(-1); // 创建工作簿对象
            Sheet sheet = workbook.createSheet(sheetName); // 创建工作表
            CellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象
            CellStyle style = getStyle(workbook); // 单元格样式对象
            int rowIndex = 0;
            // 产生表格标题行
            if (StringUtils.isNotBlank(title)) {
                Row rowm = sheet.createRow(rowIndex);
                Cell cellTiltle = rowm.createCell(0);
                // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
                cellTiltle.setCellStyle(columnTopStyle);
                cellTiltle.setCellValue(title);
                rowIndex = rowIndex + 2;
            }
            // 定义所需列数
            int columnNum = rowName.length;
            Row rowRowName = sheet.createRow(rowIndex);
            rowIndex = rowIndex + 1;
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                Cell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
                cellRowName.setCellType(Cell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text); // 设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式

                int columnWidth = sheet.getColumnWidth(n) / 256;
                int length = cellRowName.getStringCellValue().getBytes().length;
                if (columnWidth < length) {
                    sheet.setColumnWidth(n, (length - 2) * 256);
                }
            }
            if (dataList != null) {
                // 将查询出的数据设置到sheet对应的单元格中
                for (int i = 0; i < dataList.size(); i++) {
                    Object[] obj = dataList.get(i); // 遍历每个对象
                    Row row = sheet.createRow(rowIndex + i); // 创建所需的行数
                    for (int j = 0; j < obj.length; j++) {
                        Cell cell = null; // 设置单元格的数据类型
                        cell = row.createCell(j, Cell.CELL_TYPE_STRING);
                        cell.setCellStyle(style); // 设置单元格样式
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            String value = obj[j].toString();
                            if (Date.class.isInstance(obj[j])) {
                                value = DateFormatUtils.format(Date.class.cast(obj[j]), "yyyy-MM-dd HH:mm:ss");
                            }
                            cell.setCellValue(value); // 设置单元格的值

                            int columnWidth = sheet.getColumnWidth(j) / 256;
                            int length = cell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                if (length > 255) {
                                    sheet.setColumnWidth(j, 254 * 256);
                                } else {
                                    sheet.setColumnWidth(j, (length - 2) * 256);
                                }
                            }
                        } else {
                            cell.setCellValue("");
                        }
                    }
                }
            }
            if (workbook != null) {
                try {
                    workbook.write(out);
                    return true;
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    out.close();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean exportWithStyle(OutputStream out, String sheetName, String title, String[] rowName,
                                          List<Object[]> dataList, HSSFWorkbook workbook) {
        try {
            HSSFSheet sheet = workbook.createSheet(sheetName); // 创建工作表
            CellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象
            int rowIndex = 0;
            // 产生表格标题行
            if (StringUtils.isNotBlank(title)) {
                HSSFRow rowm = sheet.createRow(rowIndex);
                HSSFCell cellTiltle = rowm.createCell(0);
                // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
                cellTiltle.setCellStyle(columnTopStyle);
                cellTiltle.setCellValue(title);
                rowIndex = rowIndex + 2;
            }
            // 定义所需列数
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(rowIndex);
            rowIndex = rowIndex + 1;
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text); // 设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式

                int columnWidth = sheet.getColumnWidth(n) / 256;
                int length = cellRowName.getStringCellValue().getBytes().length;
                if (columnWidth < length) {
                    sheet.setColumnWidth(n, (length - 2) * 256);
                }
            }

            // 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {

                Object[] obj = dataList.get(i); // 遍历每个对象
                HSSFRow row = sheet.createRow(rowIndex + i); // 创建所需的行数

                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null; // 设置单元格的数据类型
                    Map<String, Object> map = (Map<String, Object>) obj[j];
                    cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle((HSSFCellStyle) map.get(STYLE)); // 设置单元格样式
                    String data = "";
                    if (map.get(DATA) != null && Date.class.isInstance(map.get(DATA))) {
                        data = DateFormatUtils.format(Date.class.cast(map.get(DATA)), "yyyy-MM-dd HH:mm:ss");
                    } else {
                        Object temp = map.get(DATA);
                        if (temp != null) {
                            data = temp.toString();
                        } else {
                            data = "";
                        }
                    }
                    if (!"".equals(data) && data != null) {
                        String value = data.toString();
                        cell.setCellValue(value); // 设置单元格的值
                        int columnWidth = sheet.getColumnWidth(j) / 256;
                        int length = cell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            if (length > 255) {
                                sheet.setColumnWidth(j, 254 * 256);
                            } else {
                                sheet.setColumnWidth(j, (length - 2) * 256);
                            }
                        }
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
//			 让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                int rowTotal = sheet.getLastRowNum();
                for (int rowNum = 0; rowNum <= rowTotal; rowNum++) {
                    HSSFRow currentRow;
                    // 当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }

            if (workbook != null) {
                try {
                    workbook.write(out);
                    return true;
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    out.close();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static File exportWithStyle(String sheetName, String title, String[] rowName,
                                       List<Object[]> dataList, HSSFWorkbook workbook, String path) {
        //创建excel文件
        File file = new File(path);
        try {
            HSSFSheet sheet = workbook.createSheet(sheetName); // 创建工作表
            CellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象
            int rowIndex = 0;
            // 产生表格标题行
            if (StringUtils.isNotBlank(title)) {
                HSSFRow rowm = sheet.createRow(rowIndex);
                HSSFCell cellTiltle = rowm.createCell(0);
                // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
                cellTiltle.setCellStyle(columnTopStyle);
                cellTiltle.setCellValue(title);
                rowIndex = rowIndex + 2;
            }
            // 定义所需列数
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(rowIndex);
            rowIndex = rowIndex + 1;
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text); // 设置列头单元格的值
                cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式

                int columnWidth = sheet.getColumnWidth(n) / 256;
                int length = cellRowName.getStringCellValue().getBytes().length;
                if (columnWidth < length) {
                    sheet.setColumnWidth(n, (length - 2) * 256);
                }
            }

            // 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {

                Object[] obj = dataList.get(i); // 遍历每个对象
                HSSFRow row = sheet.createRow(rowIndex + i); // 创建所需的行数

                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null; // 设置单元格的数据类型
                    Map<String, Object> map = (Map<String, Object>) obj[j];
                    cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle((HSSFCellStyle) map.get(STYLE)); // 设置单元格样式
                    String data = "";
                    if (map.get(DATA) != null && Date.class.isInstance(map.get(DATA))) {
                        data = DateFormatUtils.format(Date.class.cast(map.get(DATA)), "yyyy-MM-dd HH:mm:ss");
                    } else {
                        Object temp = map.get(DATA);
                        if (temp != null) {
                            data = temp.toString();
                        } else {
                            data = "";
                        }
                    }
                    if (!"".equals(data) && data != null) {
                        String value = data.toString();
                        cell.setCellValue(value); // 设置单元格的值
                        int columnWidth = sheet.getColumnWidth(j) / 256;
                        int length = cell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            if (length > 255) {
                                sheet.setColumnWidth(j, 254 * 256);
                            } else {
                                sheet.setColumnWidth(j, (length - 2) * 256);
                            }
                        }
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
            // 让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                int rowTotal = sheet.getLastRowNum();
                for (int rowNum = 0; rowNum <= rowTotal; rowNum++) {
                    HSSFRow currentRow;
                    // 当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }

            if (workbook != null) {
                file.createNewFile();
                //将excel写入
                FileOutputStream stream = FileUtils.openOutputStream(file);
                try {
                    workbook.write(stream);
                    return file;
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    stream.close();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /*
     * 列头单元格样式
     */
    private static CellStyle getColumnTopStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public static CellStyle getStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        // font.setFontHeightInPoints((short)10);
        // 字体加粗
        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为左对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 设置垂直对齐的样式为上部对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        return style;
    }

    public static List<List<Object>> readExcel(Workbook workbook) {
        return readExcel(workbook, 0, 1);
    }

    public static List<List<Object>> readExcel(Workbook workbook, Integer sheetNum, Integer startRoowNum) {
        List<List<Object>> result = null;
        if (workbook != null) {
            result = new ArrayList<List<Object>>();
            //获取第一个sheet
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //获取最大行数
            int rownum = sheet.getLastRowNum();
            for (int i = startRoowNum; i <= rownum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    List<Object> item = new ArrayList<Object>();
                    //获取最大列数
                    int colnum = row.getLastCellNum();
                    for (int j = 0; j < colnum; j++) {
                        Object obj = getCellValue(row.getCell(j));
                        item.add(obj);
                    }
                    result.add(item);
                }
            }
        }
        return result;
    }

    public static List<Map<String, Object>> readExcel(Workbook workbook, String[] names) {
        return readExcel(workbook, names, 0, 1);
    }

    public static List<Map<String, Object>> readExcel(Workbook workbook, String[] names, Integer sheetNum, Integer startRoowNum) {
        List<Map<String, Object>> result = null;
        if (workbook != null) {
            result = new ArrayList<Map<String, Object>>();
            //获取第一个sheet
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //获取最大行数
            int rownum = sheet.getLastRowNum();
            for (int i = startRoowNum; i <= rownum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Map<String, Object> map = new LinkedHashMap<String, Object>();
                    //获取最大列数
                    int colnum = row.getLastCellNum();
                    for (int j = 0; j < colnum; j++) {
                        Object obj = getCellValue(row.getCell(j));
                        if (names.length > j) {
                            map.put(names[j], obj);
                        }
                    }
                    result.add(map);
                }
            }
        }
        return result;
    }

    public static <T> List<T> readExcel(Workbook workbook, Class<T> pClazz, String[] names) {
        return readExcel(workbook, pClazz, names, 0, 1);
    }

    public static <T> List<T> readExcel(Workbook workbook, final Class<T> pClazz, final String[] names, Integer sheetNum, Integer startRoowNum) {
        List<T> result = readExcel(workbook, sheetNum, startRoowNum, new ReadListener<T>() {
            @Override
            public T readRow(Row row, Integer rowIndex, Integer maxCellNum) {
                try {
                    T entity = pClazz.newInstance();
                    return entity;
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                return null;
            }

            @Override
            public T readCell(T entity, Row row, Cell cell, Integer cellIndex, Object value) {
                if (names.length > cellIndex && value != null) {
                    String name = names[cellIndex];
                    if (!ClassTools.setFieldValueBySetMethod(entity, name, value)) {
                        //未成功，使用其它方式
                        if (!ClassTools.setFieldValueBySetMethod(entity, name, String.class, value)) {
                            if (!ClassTools.setFieldValueBySetMethod(entity, name, Integer.class, value)) {
                                if (!ClassTools.setFieldValueBySetMethod(entity, name, Long.class, value)) {
                                    if (!ClassTools.setFieldValueBySetMethod(entity, name, Double.class, value)) {
                                        if (!ClassTools.setFieldValueBySetMethod(entity, name, Boolean.class, value)) {
                                            //放弃
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                return entity;
            }
        });
        return result;
    }

    public static <T> List<T> readExcel(Workbook workbook, Integer sheetNum, Integer startRoowNum, ReadListener<T> readListener) {
        List<T> result = null;
        if (workbook != null) {
            result = new ArrayList<T>();
            //获取第一个sheet
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //获取最大行数
            int rownum = sheet.getLastRowNum();
            for (int i = startRoowNum; i <= rownum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    //获取最大列数
                    int maxCellNum = row.getLastCellNum();
                    T entity = readListener.readRow(row, i, maxCellNum);
                    if (entity != null) {
                        for (int j = 0; j < maxCellNum; j++) {
                            Cell cell = row.getCell(j);
                            Object obj = getCellValue(cell);
                            readListener.readCell(entity, row, cell, j, obj);
                        }
                        result.add(entity);
                    }
                }
            }
        }
        return result;
    }

    /**
     * 读取Excel文档
     *
     * @param fileName
     * @param inStream
     * @return
     */
    public static Workbook readExcelToWorkbook(String fileName, InputStream inStream) {
        Workbook wb = null;
        if (inStream == null) {
            return null;
        }
        String extString = FilenameUtils.getExtension(fileName);
        try {
            if ("xls".equals(extString)) {
                return wb = new HSSFWorkbook(inStream);
            } else if ("xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(inStream);
            } else {
                return wb = null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    public static interface ReadListener<T> {

        public T readRow(Row row, Integer rowIndex, Integer maxCellNum);

        public T readCell(T entity, Row row, Cell cell, Integer cellIndex, Object value);
    }
}
